﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using League.Entity;
using League.DBUtility;
using System.Data;
using System.Data.SqlClient;

namespace League.DataAccess
{
    public class LeagueMatchDataAccess
    {
        private static LeagueSQLEntityHelper<LeagueMatch> sqlEntityHelper = new LeagueSQLEntityHelper<LeagueMatch>();
        public static string Insert(LeagueMatch leagueMatch)
        {
            string result = string.Empty;
            SqlParameter[] parms = {
                                       //new SqlParameter("SeasonYear", leagueMatch.SeasonYear),
                                       new SqlParameter("LeagueID",leagueMatch.LeagueID),
                                       new SqlParameter("HomeTeamID",leagueMatch.HomeTeamID),
                                       new SqlParameter("VisitingTeamID",leagueMatch.VisitingTeamID),
                                       new SqlParameter("RoundNumber",leagueMatch.RoundNumber),
                                       new SqlParameter("MatchResult", leagueMatch.MatchResult),
                                       new SqlParameter("MatchDateTime",leagueMatch.MatchDateTime)
                                   };
            result = LeagueSQLHelper.ExecuteScalar(CommandType.StoredProcedure,
                "LeagueMatchInsert", parms).ToString();
            return result;
        }

        public static List<LeagueMatch> GetMatchByTeamID(string teamID, int matchCount)
        {
            List<LeagueMatch> result = new List<LeagueMatch>();

            string sql = string.Format(@"SELECT * 
	                                    FROM LeagueMatch match
		                                    LEFT JOIN LeagueTeamData teamData ON match.MatchID = teamData.MatchID
	                                    WHERE teamData.MatchID IN
		                                    (SELECT MatchID FROM LeagueMatch match 
		                                    WHERE match.HomeTeamID = '{0}'
		                                        OR match.VisitingTeamID = '{0}')
	                                    ORDER BY match.MatchDateTime, teamData.MatchID",teamID);
            result = LeagueSQLEntityHelper<LeagueMatch>.GetEntitySet(sql);

            return result;
        }

        public static MatchDataStats GetMatchDataStats(string leagueId, string leagueTeamId, int begin, int end)
        {
            MatchDataStats result = new MatchDataStats();
            string sql = string.Format("SELECT SUM(HomeGoal) AS GoalCount, SUM(VisitingGoal) AS LoseCount," +
                        " COUNT(3) AS MatchWinCount, COUNT(1) AS MatchDrawCount, COUNT(0) AS MatchLoseCount " + 
                        " FROM Fun_GetMatchData('{0}')" +
                        " WHERE HomeTeamID = '{1}' AND RoundNumber BETWEEN {2} AND {3}", 
                        leagueId, leagueTeamId, begin, end);
            result = LeagueSQLEntityHelper<MatchDataStats>.GetEntity(sql);

            return result;
        }

        public static List<LeagueMatch> GetMatchByRoundNumber(string leagueId, int roundNumber)
        {
            List<LeagueMatch> result = new List<LeagueMatch>();

            string sql = string.Format("SELECT * FROM LeagueMatch " + 
                                        " WHERE LeagueId='{0}' AND RoundNumber = {1} " + 
                                        " ORDER BY RoundNumber", leagueId, roundNumber);
            result = LeagueSQLEntityHelper<LeagueMatch>.GetEntitySet(sql);

            return result;
        }        
    }

    public class MatchDataStats
    {
        public int MatchLoseCount { get; set; }
        public int MatchWinCount { get; set; }
        public int MatchDrawCount { get; set; }
        public int GoalCount { get; set; }
        public int LoseCount { get; set; }
    }
}
